# -*- coding: UTF-8 -*-
'''
@Project ：python_selenium 
@File ：oracle_sql_tms.py
@Author ：dqr
@Date ：2021/8/10 16:59 
'''
# 数据库脚本
import cx_Oracle


def connectOracle(oracleType):
    """
    数据库链接
    :param oracleType: 数据库类型
    :return:
    """
    if oracleType == 'TMS':
        oracleDB = cx_Oracle.connect(r'TMS_USER/TMS_USER@172.29.30.142:1521/tmstest')  # 用户密码主机端口SI
    if oracleType == 'TOOLS':
        oracleDB = cx_Oracle.connect(r'sjtools_user/sjtools_user@192.168.111.86:1521/toolsdb')  # 用户密码主机端口SI
    if oracleType == 'WMS':
        oracleDB = cx_Oracle.connect(r'HD40/HD40@172.29.30.135:1522/ydchdts')
    return oracleDB


def initTMS(status, orderNo, sqlType):
    """
    链接tms数据库 初始化tms数据
    :param status: 装车状态
    :param orderNo: 装车单号
    :param sqlType: 执行sql-update/select
    :return: tuple元组结果
    """
    status = str(status)
    con = connectOracle('TMS')
    cursor = cx_Oracle.Cursor(con)
    if sqlType != "update":
        pass
    else:
        # 更新装车单
        updateSql = "update doc_bl_load_header set dis_status = " + status + " where BL_LOAD_NO = '" + orderNo + "'"
        cursor.execute(updateSql)
        con.commit()
    # 查找装车单
    sql = "select BL_LOAD_NO,DIS_STATUS,EMS_NO from doc_bl_load_header where BL_LOAD_NO = '" + orderNo + "'"
    cursor.execute(sql)
    tupleList = cursor.fetchone()
    print("TMS装车单号元组：", tupleList)
    cursor.close()
    con.close
    return tupleList


# 链接tools数据库 初始化tools数据
def initTOOLS(status, orderNo, sqlType):
    """
    链接tools数据库 初始化tools数据
    :param status: 装车状态
    :param orderNo: 装车单号
    :param sqlType: 执行sql-update/select
    :return: tuple元组结果
    """
    status = str(status)
    con = connectOracle('TOOLS')
    cursor = cx_Oracle.Cursor(con)
    if sqlType != "update":
        pass
    else:
        updateSql = "update BL_LST_LOAD_HEADER set STATUS = " + status + " where BL_LOAD_NO = '" + orderNo + "'"
        cursor.execute(updateSql)
        con.commit()
        # 收筐数据
        updateSql = "update BL_LST_LOAD_DETAILS set RETURNBOX_QTY = 0 where BL_LOAD_NO = '" + orderNo + "'"
        cursor.execute(updateSql)
        con.commit()
        # 删除收筐记录
        deleteSql = "delete from BL_LST_LOAD_RECEIVEBOXRECORD where BL_LOAD_NO = '" + orderNo + "'"
        cursor.execute(deleteSql)
        con.commit()
        #删除回筐录入记录
        deleteSql = "delete from BL_LST_LOAD_RECEIVEBOX where BL_LOAD_NO = '"+orderNo+"'"
        cursor.execute(deleteSql)
        con.commit()
    sql = "select BL_LOAD_NO,STATUS,OUT_DOCK from BL_LST_LOAD_HEADER where BL_LOAD_NO = '" + orderNo + "'"
    cursor.execute(sql)
    tupleList = cursor.fetchone()
    print("TOOLS装车单号元组：", tupleList)
    cursor.close()
    con.close
    return tupleList

def initWMS(orderNo):
    arrangeCarNo = getWMSOrderNo(orderNo)
    con = connectOracle('WMS')
    cursor = cx_Oracle.Cursor(con)
    sql = "delete from TSTORERTNCONTAINERINFO where FSHIPNUM = '"+arrangeCarNo+"'"
    cursor.execute(sql)
    con.commit()
    cursor.close()
    con.close()


def toolsCollectBasketNum(orderNo, shopNo):
    """
    搜索收筐记录
    :param orderNo: 装车状态
    :param shopNo: 门店号
    :return: yes/no 验证是否成功
    """
    con = connectOracle('TOOLS')
    cursor = cx_Oracle.Cursor(con)
    sql = "select BL_LOAD_NO,STORE_NO,RETURNBOX_QTY from BL_LST_LOAD_DETAILS where BL_LOAD_NO = '" + orderNo + "'"
    cursor.execute(sql)
    tupleList = cursor.fetchall()
    cursor.close()
    con.close()
    boolCollectBasketNum(tupleList, shopNo)
    return boolCollectBasketNum(tupleList, shopNo)


def boolCollectBasketNum(tupleList, shopNo):
    """
    判断数据库中的数量和实际录入的数量是否一致
    :param tupleList: 数据库数据
    :param shopNo: 实际录入的门店号
    :return: yes/no 是否一致
    """
    basketNum = []
    for data in tupleList:
        no = str(data[1])
        if no in shopNo:
            if str(data[2]) == '1':
                basketNum.append('true')
            else:
                basketNum.append('false')
        else:
            pass
    if 'false' in basketNum:
        return 'no'
    else:
        return 'yes'

def initReturnWareHouse(orderNo):
    """
    模拟刷卡回厂状态，80状态
    :param orderNo: 单号
    :return:
    """
    con = connectOracle('TMS')
    cursor = cx_Oracle.Cursor(con)
    sql = "update doc_bl_load_header set dis_status = 80 where BL_LOAD_NO = '"+orderNo+"'"
    cursor.execute(sql)
    con.commit()
    cursor.close()
    con.close()

    con = connectOracle('TOOLS')
    cursor = cx_Oracle.Cursor(con)
    sql = "update BL_LST_LOAD_HEADER set STATUS = 80 where BL_LOAD_NO = '" + orderNo + "'"
    cursor.execute(sql)
    con.commit()
    cursor.close()
    con.close()


def getWMSOrderNo(orderNo):
    """
    查询WMS拣货单
    :param orderNo: 运送单
    :return: 拣货单
    """
    con = connectOracle('WMS')
    cursor = cx_Oracle.Cursor(con)
    sql = "select num from tship where fplannum = (select num from tshipplan where fsrcnum = '"+orderNo+"')"
    cursor.execute(sql)
    pickOrderNo = cursor.fetchone()
    cursor.close()
    con.close()
    print("排车单号："+str(pickOrderNo[0]))
    return str(pickOrderNo[0])

